﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using System.Data.SqlClient;

namespace LOS.Redirects
{
    public partial class Admin : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string strSiteName = CleanSiteName(txtName.Text.Trim());

            //## A simple check to make sure all the required fields are filled out
            if (txtName.Text == "" || txtUrl.Text == "" || txtRequestor.Text == "")
            {
                lblError.Text = "Make sure to fill in all fields.";
                lblError.Visible = true;

                return;
            }

            //## Check if the Alias already exists.
            if (SiteNameExists(strSiteName))
            {
                //## Display the Name Error Label.
                lblError.Text = "This alias name already exists in the database.";
                lblError.Visible = true;
            }
            else
            {
                //## Reset the Name Error Label
                lblError.Text = "";
                lblError.Visible = false;

                //## Get the SQL connection string from web.config
                SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString);

                //## Create the SQL insert statement
                string strSqlQuery = "INSERT INTO Redirects (name, url, requestorLogin, active, created) VALUES ('"
                    + strSiteName + "', '"
                    + txtUrl.Text.Trim() + "', '"
                    + txtRequestor.Text.Trim() + "', '"
                    + chkActive.Checked + "', '"
                    + DateTime.Now.ToShortDateString() + "')";

                try
                {                    
                    using (SqlCommand sqlCmd = new SqlCommand())
                    {
                        //## Set the connection and query for the SQL command
                        sqlCmd.Connection = sqlConn;
                        sqlCmd.CommandText = strSqlQuery;

                        //## Open the SQL Connection
                        sqlConn.Open();
                        //## Execute the insert and return the number of records affected. Should be "1".
                        int intRecordsAffected = sqlCmd.ExecuteNonQuery();
                        //## Close the SQL Connection
                        sqlConn.Close();

                        if (intRecordsAffected > 0)
                        {
                            //## Refresh the page.
                            Response.Redirect("/admin.aspx");
                        }
                        else
                        {
                            //## No records were affected so there was some sort of error.
                            lblError.Text = "There was an error. Records affected from insert: " + intRecordsAffected.ToString();
                            lblError.Visible = true;
                        }
                    }
                    
                }
                catch (Exception ex)
                {
                    lblError.Text = ex.Message;
                    lblError.Visible = true;
                }
            }
        }

        private bool SiteNameExists(string SiteName)
        {
            //## Get the SQL connection string from web.config
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString);

            //## Create the SQL query
            string strSqlQuery = "SELECT name FROM Redirects WHERE name ='" + SiteName + "'";

            //## Create a new SQL command object
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                //## Set the connection and query for the SQL command
                sqlCmd.Connection = sqlConn;
                sqlCmd.CommandText = strSqlQuery;

                //## Open the SQL Connection
                sqlConn.Open();
                //## Execute the query and return a boolean based on if the data exists or not.
                bool blnNameExists = sqlCmd.ExecuteReader().HasRows;
                //## Close the SQL Connection
                sqlConn.Close();

                return blnNameExists;
            }            
        }

        private bool SiteNameExists(string SiteName, string AliasId)
        {
            bool blnNameExists = false;

            //## Get the SQL connection string from web.config
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString);

            //## Create the SQL query
            string strSqlQuery = "SELECT id, name FROM Redirects WHERE name ='" + SiteName + "'";

            //## Create a new SQL command object
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                //## Set the connection and query for the SQL command
                sqlCmd.Connection = sqlConn;
                sqlCmd.CommandText = strSqlQuery;

                //## Open the SQL Connection
                sqlConn.Open();

                //## Execute the query and return a boolean based on if the data exists or not.                
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();

                while (sqlReader.Read())
                {
                    if (sqlReader.HasRows)
                    {
                        if (!sqlReader.IsDBNull(0))
                        {
                            if (string.Format("{0}", sqlReader[0]) == AliasId)
                            {
                                blnNameExists = false;
                            }
                            else
                            {
                                blnNameExists = true;
                            }

                        }
                        else
                        {
                            blnNameExists = false;
                        }
                    }
                    else
                    {
                        blnNameExists = false;
                    }
                }

                
                //## Close the SQL Connection
                sqlConn.Close();

                return blnNameExists;
            }
        }

        private string CleanSiteName(string SiteName)
        {

            //## Find out if the domain is in the string
            if (txtName.Text.Contains("." + ConfigurationManager.AppSettings["Domain"]))
            {
                //## Remove the domain. Expample ".landofsharepoint.com"
                return SiteName.Substring(0, SiteName.IndexOf("." + ConfigurationManager.AppSettings["Domain"]));
            }

            return SiteName;
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            //## Reset the Name Error Label
            lblError_search.Text = "";
            lblError_search.Visible = false;

            //## Reset the Update button.
            btnUpdate.Visible = false;

            //## Get the SQL connection string from web.config
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString);

            //## Create the SQL insert statement
            string strSqlQuery = "SELECT id, url, requestorLogin, active FROM Redirects WHERE name = '" + txtName_search.Text.Trim() + "'";

            try
            {
                using (SqlCommand sqlCmd = new SqlCommand())
                {
                    //## Set the connection and query for the SQL command
                    sqlCmd.Connection = sqlConn;
                    sqlCmd.CommandText = strSqlQuery;

                    //## Open the SQL Connection
                    sqlConn.Open();
                    //## Execute the query and put the results into a reader
                    SqlDataReader sqlReader = sqlCmd.ExecuteReader();                   

                    if (!sqlReader.HasRows)
                    {
                        //## No records were returned so there was some sort of error.
                        lblError_search.Text = "The alias was not found.";
                        lblError_search.Visible = true;
                    }
                    else
                    {
                        while (sqlReader.Read())
                        {
                            //## If the ID is null, we do not want to go on.
                            if (!sqlReader.IsDBNull(0))
                            {
                                //## id
                                hidId.Value = string.Format("{0}", sqlReader[0]);

                                //## url
                                if (!sqlReader.IsDBNull(1))
                                    txtUrl_search.Text = string.Format("{0}", sqlReader[1]);

                                //## requestorLogin
                                if (!sqlReader.IsDBNull(2))
                                    txtRequestor_search.Text = string.Format("{0}", sqlReader[2]);

                                //## active
                                if (!sqlReader.IsDBNull(3))
                                {
                                    if (string.Format("{0}", sqlReader[3]).ToLower() == "true")
                                    {
                                        chkActive_search.Checked = true;
                                    }
                                    else
                                    {
                                        chkActive_search.Checked = false;
                                    }
                                }

                                //## We populated all data, so show the Update button.
                                btnUpdate.Visible = true;
                            }
                            else
                            {
                                lblError_search.Text = "There was a problem and the alias you search for could not be retrieved properly.";
                                lblError_search.Visible = true;
                            }
                        }
                                             
                    }

                    //## Close the SQL Connection
                    sqlConn.Close();
                }
            }
            catch (Exception ex)
            {
                lblError_search.Text = ex.Message;
                lblError_search.Visible = true;
            }

        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            //## Reset the Name Error Label
            lblError_search.Text = "";
            lblError_search.Visible = false;

            //## A simple check to make sure all the required fields are filled out
            if (txtName_search.Text == "" || txtUrl_search.Text == "" || txtRequestor_search.Text == "")
            {
                lblError_search.Text = "Make sure to fill in all fields.";
                lblError_search.Visible = true;

                return;
            }

            if (!SiteNameExists(txtName_search.Text.Trim(), hidId.Value))
            {

                //## Get the SQL connection string from web.config
                SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString);

                //## Create the SQL insert statement
                string strSqlQuery = "UPDATE Redirects SET name='" + txtName_search.Text.Trim() + "', "
                    + "url = '" + txtUrl_search.Text.Trim() + "', "
                    + "requestorLogin = '" + txtRequestor_search.Text.Trim() + "', "
                    + "active = '" + chkActive_search.Checked + "' WHERE id='" + hidId.Value + "'";

                try
                {
                    using (SqlCommand sqlCmd = new SqlCommand())
                    {
                        //## Set the connection and query for the SQL command
                        sqlCmd.Connection = sqlConn;
                        sqlCmd.CommandText = strSqlQuery;

                        //## Open the SQL Connection
                        sqlConn.Open();
                        //## Execute the insert and return the number of records affected. Should be "1".
                        int intRecordsAffected = sqlCmd.ExecuteNonQuery();
                        //## Close the SQL Connection
                        sqlConn.Close();

                        if (intRecordsAffected > 0)
                        {
                            //## Refresh the page.
                            Response.Redirect("/admin.aspx");
                        }
                        else
                        {
                            //## No records were affected so there was some sort of error.
                            lblError.Text = "There was an error. Records affected from insert: " + intRecordsAffected.ToString();
                            lblError.Visible = true;
                        }
                    }
                }
                catch (Exception ex)
                {
                    lblError_search.Text = ex.Message;
                    lblError_search.Visible = true;
                }
            }
            else 
            {
                //## Display the Name Error Label.
                lblError_search.Text = "This alias name already exists in the database.";
                lblError_search.Visible = true;
            }

        }

    }
}
